
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Clubs_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Clubs_Insert]

IF OBJECT_ID(N'[dbo].[Clubs_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Clubs_Update]

IF OBJECT_ID(N'[dbo].[Clubs_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Clubs_Delete]

IF OBJECT_ID(N'[dbo].[Clubs_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Clubs_Select]

--endregion

GO


--region [dbo].[Clubs_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Clubs_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdClub int = null,
	@ClubName nvarchar(100) = null,
	@IdCountry int = null,
	@Province nvarchar(100) = null,
	@StadiumName nvarchar(100) = null,
	@EstablishYear int = null,
	@Capacity int = null,
	@ChairmanName nvarchar(100) = null,
	@Rate int = null,
	@Address nvarchar(1000) = null,
	@Phone nvarchar(50) = null,
	@Website nvarchar(300) = null,
	@LogoClub nvarchar(100) = null,
	@ClotheImage nvarchar(100) = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Clubs]
					WHERE
					(
						(@IdClub is null OR [Clubs].[IdClub] = @IdClub)
						AND (@ClubName is null OR [Clubs].[ClubName] like @ClubName)
						AND (@IdCountry is null OR [Clubs].[IdCountry] = @IdCountry)
						AND (@Province is null OR [Clubs].[Province] like @Province)
						AND (@StadiumName is null OR [Clubs].[StadiumName] like @StadiumName)
						AND (@EstablishYear is null OR [Clubs].[EstablishYear] = @EstablishYear)
						AND (@Capacity is null OR [Clubs].[Capacity] = @Capacity)
						AND (@ChairmanName is null OR [Clubs].[ChairmanName] like @ChairmanName)
						AND (@Rate is null OR [Clubs].[Rate] = @Rate)
						AND (@Address is null OR [Clubs].[Address] like @Address)
						AND (@Phone is null OR [Clubs].[Phone] like @Phone)
						AND (@Website is null OR [Clubs].[Website] like @Website)
						AND (@LogoClub is null OR [Clubs].[LogoClub] like @LogoClub)
						AND (@ClotheImage is null OR [Clubs].[ClotheImage] like @ClotheImage)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Clubs].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdClub' and @SortType = 1 THEN [Clubs].[IdClub] END ASC,
						CASE WHEN @SortBy = 'IdClub' and @SortType = 0 THEN [Clubs].[IdClub] END DESC,
						CASE WHEN @SortBy = 'ClubName' and @SortType = 1 THEN [Clubs].[ClubName] END ASC,
						CASE WHEN @SortBy = 'ClubName' and @SortType = 0 THEN [Clubs].[ClubName] END DESC,
						CASE WHEN @SortBy = 'IdCountry' and @SortType = 1 THEN [Clubs].[IdCountry] END ASC,
						CASE WHEN @SortBy = 'IdCountry' and @SortType = 0 THEN [Clubs].[IdCountry] END DESC,
						CASE WHEN @SortBy = 'Province' and @SortType = 1 THEN [Clubs].[Province] END ASC,
						CASE WHEN @SortBy = 'Province' and @SortType = 0 THEN [Clubs].[Province] END DESC,
						CASE WHEN @SortBy = 'StadiumName' and @SortType = 1 THEN [Clubs].[StadiumName] END ASC,
						CASE WHEN @SortBy = 'StadiumName' and @SortType = 0 THEN [Clubs].[StadiumName] END DESC,
						CASE WHEN @SortBy = 'EstablishYear' and @SortType = 1 THEN [Clubs].[EstablishYear] END ASC,
						CASE WHEN @SortBy = 'EstablishYear' and @SortType = 0 THEN [Clubs].[EstablishYear] END DESC,
						CASE WHEN @SortBy = 'Capacity' and @SortType = 1 THEN [Clubs].[Capacity] END ASC,
						CASE WHEN @SortBy = 'Capacity' and @SortType = 0 THEN [Clubs].[Capacity] END DESC,
						CASE WHEN @SortBy = 'ChairmanName' and @SortType = 1 THEN [Clubs].[ChairmanName] END ASC,
						CASE WHEN @SortBy = 'ChairmanName' and @SortType = 0 THEN [Clubs].[ChairmanName] END DESC,
						CASE WHEN @SortBy = 'Rate' and @SortType = 1 THEN [Clubs].[Rate] END ASC,
						CASE WHEN @SortBy = 'Rate' and @SortType = 0 THEN [Clubs].[Rate] END DESC,
						CASE WHEN @SortBy = 'Address' and @SortType = 1 THEN [Clubs].[Address] END ASC,
						CASE WHEN @SortBy = 'Address' and @SortType = 0 THEN [Clubs].[Address] END DESC,
						CASE WHEN @SortBy = 'Phone' and @SortType = 1 THEN [Clubs].[Phone] END ASC,
						CASE WHEN @SortBy = 'Phone' and @SortType = 0 THEN [Clubs].[Phone] END DESC,
						CASE WHEN @SortBy = 'Website' and @SortType = 1 THEN [Clubs].[Website] END ASC,
						CASE WHEN @SortBy = 'Website' and @SortType = 0 THEN [Clubs].[Website] END DESC,
						CASE WHEN @SortBy = 'LogoClub' and @SortType = 1 THEN [Clubs].[LogoClub] END ASC,
						CASE WHEN @SortBy = 'LogoClub' and @SortType = 0 THEN [Clubs].[LogoClub] END DESC,
						CASE WHEN @SortBy = 'ClotheImage' and @SortType = 1 THEN [Clubs].[ClotheImage] END ASC,
						CASE WHEN @SortBy = 'ClotheImage' and @SortType = 0 THEN [Clubs].[ClotheImage] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Clubs]
			Where 
			(
				(@IdClub is null OR [Clubs].[IdClub] = @IdClub)
				AND (@ClubName is null OR [Clubs].[ClubName] like @ClubName)
				AND (@IdCountry is null OR [Clubs].[IdCountry] = @IdCountry)
				AND (@Province is null OR [Clubs].[Province] like @Province)
				AND (@StadiumName is null OR [Clubs].[StadiumName] like @StadiumName)
				AND (@EstablishYear is null OR [Clubs].[EstablishYear] = @EstablishYear)
				AND (@Capacity is null OR [Clubs].[Capacity] = @Capacity)
				AND (@ChairmanName is null OR [Clubs].[ChairmanName] like @ChairmanName)
				AND (@Rate is null OR [Clubs].[Rate] = @Rate)
				AND (@Address is null OR [Clubs].[Address] like @Address)
				AND (@Phone is null OR [Clubs].[Phone] like @Phone)
				AND (@Website is null OR [Clubs].[Website] like @Website)
				AND (@LogoClub is null OR [Clubs].[LogoClub] like @LogoClub)
				AND (@ClotheImage is null OR [Clubs].[ClotheImage] like @ClotheImage)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Clubs_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Clubs_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Clubs_Insert]
	@IdClub int OUTPUT,
	@ClubName nvarchar(100),
	@IdCountry int,
	@Province nvarchar(100),
	@StadiumName nvarchar(100),
	@EstablishYear int,
	@Capacity int,
	@ChairmanName nvarchar(100),
	@Rate int,
	@Address nvarchar(1000),
	@Phone nvarchar(50),
	@Website nvarchar(300),
	@LogoClub nvarchar(100),
	@ClotheImage nvarchar(100)

AS


INSERT INTO [dbo].[Clubs] 
(
	[ClubName],
	[IdCountry],
	[Province],
	[StadiumName],
	[EstablishYear],
	[Capacity],
	[ChairmanName],
	[Rate],
	[Address],
	[Phone],
	[Website],
	[LogoClub],
	[ClotheImage]
)
VALUES 
(
	@ClubName,
	@IdCountry,
	@Province,
	@StadiumName,
	@EstablishYear,
	@Capacity,
	@ChairmanName,
	@Rate,
	@Address,
	@Phone,
	@Website,
	@LogoClub,
	@ClotheImage
)

SET @IdClub = SCOPE_IDENTITY()

--end [dbo].[Clubs_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Clubs_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Clubs_Update]
	@IdClub int,
	@ClubName nvarchar(100),
	@IdCountry int,
	@Province nvarchar(100),
	@StadiumName nvarchar(100),
	@EstablishYear int,
	@Capacity int,
	@ChairmanName nvarchar(100),
	@Rate int,
	@Address nvarchar(1000),
	@Phone nvarchar(50),
	@Website nvarchar(300),
	@LogoClub nvarchar(100),
	@ClotheImage nvarchar(100)
AS


UPDATE [dbo].[Clubs] SET
	[ClubName] = @ClubName,
	[IdCountry] = @IdCountry,
	[Province] = @Province,
	[StadiumName] = @StadiumName,
	[EstablishYear] = @EstablishYear,
	[Capacity] = @Capacity,
	[ChairmanName] = @ChairmanName,
	[Rate] = @Rate,
	[Address] = @Address,
	[Phone] = @Phone,
	[Website] = @Website,
	[LogoClub] = @LogoClub,
	[ClotheImage] = @ClotheImage
WHERE
	[IdClub] = @IdClub

--end [dbo].[Clubs_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Clubs_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Clubs_Delete]
	@IdClub int
AS


DELETE FROM [dbo].[Clubs]
WHERE
(
	[IdClub] = @IdClub
)

--end [dbo].[Clubs_Delete]
--endregion

GO
--=========================================================================================--

